package com.logger.export.sql;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;

import com.logger.LogFile;
import com.logger.LogFiles;
import com.logger.LogQuery;
import com.logger.LogTransaction;

public class LogUsersSQL {

    /**
     * @param args
     */
    public static void main(final String[] args) {
        // TODO Auto-generated method stub

        Connection conn = null;

        try {
            final String userName = "root";
            final String password = "";
            final String url = "jdbc:mysql://localhost/logparserdb";
            Class.forName("com.mysql.jdbc.Driver").newInstance();
            conn = DriverManager.getConnection(url, userName, password);
            System.out.println("Database connection established");
            LogUsersSQL.insert_data(conn);

        } catch (final SQLException ex) {
            System.err.println("Cannot connect to database server (SQLException)");
            System.out.println(ex.getMessage());
        } catch (final ClassNotFoundException ex) {
            ex.printStackTrace();
            System.err.println("Cannot connect to database server (ClassNotFoundException)");
            System.out.println(ex.getMessage());
        } catch (final InstantiationException ex) {
            System.err.println("Cannot connect to database server (InstantiationException)");
            System.out.println(ex.getMessage());
        } catch (final IllegalAccessException ex) {
            System.err.println("Cannot connect to database server (IllegalAccesException)");
            System.out.println(ex.getMessage());
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                    System.out.println("Database connection terminated");
                } catch (final Exception e) { /* ignore close errors */
                }
            }
        }

    }

    /**
     * @param con
     * @throws SQLException
     */
    public static void insert_data(final Connection con) throws SQLException {
        // TODO Auto-generated method stub

        final String directoryPath = "C:\\Users\\bbarhoumi\\Desktop\\stageOmar\\2013-08-27\\node2";
        final LogFiles logUsers = new LogFiles();
        logUsers.loadUserFiles(directoryPath);

        final List<LogFile> lstLogFile = logUsers;
        for (final LogFile logFile : lstLogFile) {
            final String userName = logFile.getUserName();
            final Statement stm = con.createStatement();
            final Statement stm1 = con.createStatement();
            final ResultSet res = stm1.executeQuery("select * from LOG_USER ;");
            boolean verif = false;
            while (res.next() && !verif) {
                if (res.getString(1).equals(userName)) {
                    verif = true;
                }

            }
            if (!verif) {
                stm.executeUpdate("insert into LOG_USER VALUES('" + userName + "');");
            }

        }
        {
            int max = 0;

            final Statement stm01 = con.createStatement();
            final ResultSet res01 = stm01.executeQuery("SELECT MAX(LogFile_id) FROM LOG_FILE ;");
            if (res01.getRow() == 1) {
                max = res01.getInt(1);
            }

            for (final LogFile logFile : lstLogFile) {

                logFile.getFileName();
                final Date dateCreation = logFile.getDate();
                // System.out.println(dateCreation);
                final Integer ConnId = logFile.getConnId();
                final String SessionId = logFile.getSessionId();
                final String userName = logFile.getUserName();
                final String node = logFile.getNode();
                final DateFormat dateFormat = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");

                final Statement stm = con.createStatement();
                max++;
                final String query1 = "insert into LOG_FILE(LogFile_id,LogFile_name,Date_creation,Conn_id,Session_id,user_login,node) VALUES(" + max + ", '" + userName + "' , '" + dateFormat.format(dateCreation) + "', " + ConnId + " , '" + SessionId + "' , '" + userName + "' , '" + node + "' );";

                stm.executeUpdate(query1);

                final List<LogTransaction> listLogTransaction = logFile.getListLogTransaction();
                for (final LogTransaction logTransaction : listLogTransaction) {
                    final long transaction_id = logTransaction.getId();
                    final Date transaction_DateOpen = logTransaction.getOpenDateTime();
                    final Date transaction_DateClose = logTransaction.getCloseDateTime();
                    int transaction_state = 0;

                    if (logTransaction.getCommit() == null) {
                        transaction_state = 0;

                    } else {

                        if (logTransaction.getCommit()) {
                            transaction_state = 1;
                        } else {
                            transaction_state = 2;
                        }

                    }

                    final Statement stm1 = con.createStatement();
                    final String query = "insert into LOG_TRANSACTION(Transaction_id,Transaction_start,Transaction_end,Transaction_state,LogFile_id) VALUES(" + transaction_id + " , '" + dateFormat.format(transaction_DateOpen) + "',' " + dateFormat.format(transaction_DateClose) + " ', "
                            + transaction_state + "," + max + ");";
                    stm1.executeUpdate(query);
                    LogUsersSQL.listQueriesInTransaction(con, logTransaction, max);

                }

                LogUsersSQL.listQueriesInFile(con, max, logFile);

            }

        }
    }

    private static void listQueriesInTransaction(final Connection con, final LogTransaction logTransaction, final int logfile_id) throws SQLException {
        // TODO Auto-generated method stub

        final List<LogQuery> listLogQuery1 = logTransaction.getQueries();
        for (final LogQuery logQuery1 : listLogQuery1) {
            final String query_file = logQuery1.getQueryfile();
            final String query_name = logQuery1.getQueryName();
            final String query_content = logQuery1.getRawQueryContentPreview();
            final long construcion_time = logQuery1.getConstructionTime();
            final long execution_time = logQuery1.getExecutionTime();
            final long rows_number = logQuery1.getRowsNumber();

            final Statement stm1 = con.createStatement();
            final String query1 = "insert into LOG_QUERY(Query_file,Query_name,Query_content,Construction_time,Execution_time,Rows_number,Transaction_id,LogFile_id)" + " VALUES('" + query_file + "' ,'" + query_name + "','" + query_content + "', " + construcion_time + ", " + "" + execution_time
                    + " , " + rows_number + " , " + logTransaction.getId() + "," + logfile_id + ");";
            stm1.executeUpdate(query1);

        }

    }

    private static void listQueriesInFile(final Connection con, final int max, final LogFile logFile) throws SQLException {
        // TODO Auto-generated method stub

        final List<LogQuery> listLogQuery2 = logFile.getListLogQuery();
        for (final LogQuery logQuery2 : listLogQuery2) {
            final String query_file2 = logQuery2.getQueryfile();
            final String query_name2 = logQuery2.getQueryName();
            final String query_content2 = logQuery2.getRawQueryContentPreview();
            final long construcion_time2 = logQuery2.getConstructionTime();
            final long execution_time2 = logQuery2.getExecutionTime();
            final long rows_number2 = logQuery2.getRowsNumber();
            final int Transaction_id = -1;
            final Statement stm2 = con.createStatement();
            final String query2 = "insert into LOG_QUERY(Query_file,Query_name,Query_content,Construction_time,Execution_time,Rows_number,Transaction_id,LogFile_id)" + " VALUES('" + query_file2 + "' ,'" + query_name2 + "','" + query_content2 + "', " + construcion_time2 + ", " + "" + execution_time2
                    + " , " + rows_number2 + " , " + Transaction_id + "," + max + ");";
            stm2.executeUpdate(query2);
        }

    }

}
